package io;

import com.alibaba.excel.EasyExcel;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.junit.Test;
import org.springframework.util.CollectionUtils;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;


public class ExcelUtil {


    public static void readExcelFile() {
        try {
            Workbook workbook = WorkbookFactory.create(new File("/Users/zhfan/Downloads/target_data.xlsx"));
            // Retrieving the number of sheets in the Workbook
            System.out.println("Workbook has " + workbook.getNumberOfSheets() + " Sheets : ");
            // Getting the Sheet at index zero
            Sheet sheet = workbook.getSheetAt(1);
            int counter = 0;
            for (Row row : sheet) {
                if (counter++ >= 10) {
                    break;
                }

                for (int i = 0; i < row.getLastCellNum() - 1; i++) {
                    System.out.print(getCellFromRowByIndex(row, i) + " | ");
                }
                System.out.println("");
            }

        } catch (Exception e) {
            System.err.println("transaction got error!" + e);
        }
    }

    public static String getCellFromRowByIndex(Row row, int index) {
        Cell cell = row.getCell(index);
        return getValue(cell);
    }

    private static String getValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        if (CellType.BOOLEAN == cell.getCellTypeEnum()) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (CellType.NUMERIC == cell.getCellTypeEnum()) {
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                DataFormatter formatter = new DataFormatter();
                return formatter.formatCellValue(cell);
            }
            DecimalFormat df = new DecimalFormat("0");
            return df.format(cell.getNumericCellValue());
        } else {
            return cell.toString();
        }
    }

    public static void readCSV(String csvFile) {
//        String csvFile = "/Users/mkyong/csv/country.csv";
        BufferedReader br = null;
        String line = "";

        try {
            br = new BufferedReader(new FileReader(csvFile));
            while ((line = br.readLine()) != null) {
                if (StringUtils.isBlank(line) || line.length() <= 4) continue;
                String[] rowSplit = line.split(",");
                System.out.println(Arrays.toString(rowSplit));
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (br != null) {
                try {
                    br.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Test
    public void test() {
        // 生成Excel路径
        String fileName = "C:\\Users\\likun\\Desktop\\测试.xlsx";
        EasyExcel.write(fileName).head(head()).sheet("模板").doWrite(dataList());
    }

    private List<List<String>> head() {
        List<List<String>> list = new ArrayList<>();
        List<String> head0 = new ArrayList<>();
        head0.add("姓名");
        List<String> head1 = new ArrayList<>();
        head1.add("年龄");
        List<String> head2 = new ArrayList<>();
        head2.add("生日");
        list.add(head0);
        list.add(head1);
        list.add(head2);
        return list;
    }

    private List<List<Object>> dataList() {
        List<List<Object>> list = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            List<Object> data = new ArrayList<>();
            data.add("张三");
            data.add(25);
            data.add(new Date());
            list.add(data);
        }
        return list;
    }

    public static void main(String[] args) throws Exception {
        readExcelFile();
//        readCSV("/Users/zhfan/Downloads/target_data.csv");
    }

}
